﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
using Microsoft.VisualBasic.FileIO;
using System.Data.SQLite;
using System.Runtime.Serialization.Json;
using Newtonsoft.Json;
using System.Web.Script.Serialization;

namespace ABDLight
{
    class SQLiteData
    {

        public static SQLiteConnection sql_con;
        private static SQLiteCommand sql_cmd;
        private static SQLiteDataAdapter DB;
        private static DataSet DS = new DataSet();
        private static DataTable DT = new DataTable();

        public static void DBCreate()
        {
            string databaseName = ABDLight.Properties.Settings.Default.SQLiteDB;
            try
            {
                sql_con = new SQLiteConnection(string.Format("Data Source={0};", databaseName));
                sql_con.Open();
                string txtQuery = "create table IF NOT EXISTS t_TableInfo ([Имя файла] text,[Полный путь] text,[Дата] text,iqStartInd INTEGER,iqnAll INTEGER,iqnGood INTEGER,iqnBad INTEGER,iqnSomn INTEGER,iqStartClean INTEGER)";
                ExecuteQuery(txtQuery);
                sql_con.Close();


                //MessageBox.Show("База удачно создана", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (Exception ex)
            {
                //MessageBox.Show("Не удалось создать базу!\nОшибка:\n" + ex.ToString(), "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public static void SetConnection()
        {
            //if (sql_con.State == ConnectionState.Open)
            //    sql_con.Close();

            string databaseName = ABDLight.Properties.Settings.Default.SQLiteDB;
            sql_con = new SQLiteConnection(string.Format("Data Source={0};", databaseName));
        }

        //Общая функции для выполнения запросов - ExecuteQuery:

        public static void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        public static void ExecuteQueryNoConn(string txtQuery,SQLiteConnection sql_con_thread)
        {
            sql_cmd = sql_con_thread.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
        }
        public static void CreateTable(DataTable dt, string TabName)
        {
            string param = "";
            foreach (DataColumn DT in dt.Columns)
            {
                if (DT.ColumnName == "isChanget" || DT.ColumnName == "isBad" || DT.ColumnName == "isCheck")
                    param += "[" + DT.ColumnName + "] integer,";
                else
                    param += "[" + DT.ColumnName + "] TEXT,";
            }

            ExecuteQuery("DROP TABLE IF EXISTS [" + TabName + "];");
            ExecuteQuery("DROP TABLE IF EXISTS [" + TabName + "_servresult];");
            ExecuteQuery("delete from t_TableInfo where [Полный путь] = '" + TabName + "'");

            ExecuteQuery("CREATE TABLE IF NOT EXISTS [" + TabName + "] (" + param.Substring(0, param.Length - 1) + ");");
            ExecuteQuery("CREATE TABLE IF NOT EXISTS [" + TabName + "_servresult] ([ID_IQ] TEXT,[service_result_json] TEXT);");
            ExecuteQuery("insert into t_TableInfo([Имя файла],[Полный путь],[Дата],iqStartInd) values('" + ABDLight.Properties.Settings.Default.CleanTableName + "','" + TabName + "','" + DateTime.Now.ToString() + "'," + frmClean.cCfg.StartInd + ")");
        }

        public static void InsertTable(DataTable dt, string TabName)
        {
            SetConnection();
            sql_con.Open();

                 
            using (SQLiteTransaction transSQLite = sql_con.BeginTransaction())
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    sql_cmd = sql_con.CreateCommand();

                    string val = "";
                    string valserv = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        val += "'" + dt.Rows[i][j].ToString().Replace("'", "''") + "',";
                        if(dt.Columns[j].ColumnName == "ID_IQ")
                            valserv = "'" + dt.Rows[i][j].ToString().Replace("'", "''") + "',''";
                    }

                    sql_cmd.CommandText = "INSERT INTO [" + TabName + "] VALUES(" + val.Substring(0, val.Length - 1) + ")";
                    sql_cmd.ExecuteNonQuery();
                    sql_cmd.CommandText = "INSERT INTO [" + TabName + "_servresult] VALUES(" + valserv + ")";
                    sql_cmd.ExecuteNonQuery();
                }
                transSQLite.Commit();
            }

            sql_con.Close();

        }

        public static void DropTable(string TabName)
        {
            string txtQuery = "DROP TABLE IF EXISTS [" + TabName + "]";
            ExecuteQuery(txtQuery);
        }

        public static void UpdateTable(DataTable dt, string TabName)
        {
            SetConnection();
            sql_con.Open();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sql_cmd = sql_con.CreateCommand();

                string val = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    val += "[" + dt.Columns[j].ColumnName + "] = '" + dt.Rows[i][j].ToString().Replace("'", "''") + "',";
                }

                sql_cmd.CommandText = "UPDATE [" + TabName + "] SET " + val.Substring(0, val.Length - 1) + " WHERE [ID_IQ] = " + dt.Rows[i]["ID_IQ"].ToString();
                sql_cmd.ExecuteNonQuery();

                sql_cmd.CommandText = "UPDATE t_TableInfo SET iqnAll = " + frmClean.cCfg.nAll + " ,iqnGood = " + frmClean.cCfg.nGood + ",iqnBad = " + frmClean.cCfg.nBad + ",iqnSomn = " + frmClean.cCfg.nSomn + ",iqStartClean = " + frmClean.cCfg.ErrorRowIndex + " where [Полный путь] = '" + TabName + "'";
                sql_cmd.ExecuteNonQuery();
            }
            sql_con.Close();

        }

        public static void UpdateRow(DataRowView AddrString, string TabName)
        {
            SetConnection();
            sql_con.Open();

            sql_cmd = sql_con.CreateCommand();
            
            string val = "";
            for (int j = 0; j < AddrString.DataView.Table.Columns.Count; j++)
            {
                val += "[" + AddrString.DataView.Table.Columns[j].ColumnName + "] = '" + AddrString.Row[j].ToString().Replace("'", "''").Replace("True", "1").Replace("False", "0") + "',";
            }

            sql_cmd.CommandText = "UPDATE [" + TabName + "] SET " + val.Substring(0, val.Length - 1) + " WHERE [ID_IQ] = " + AddrString.Row["ID_IQ"].ToString();
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = "UPDATE t_TableInfo SET iqnAll = " + frmClean.cCfg.nAll + " ,iqnGood = " + frmClean.cCfg.nGood + ",iqnBad = " + frmClean.cCfg.nBad + ",iqnSomn = " + frmClean.cCfg.nSomn + ",iqStartClean = " + frmClean.cCfg.StartRow + " where [Полный путь] = '" + TabName + "'";
            sql_cmd.ExecuteNonQuery();
            
            sql_con.Close();

        }
        public static DataTable GetDataTable(string TabName)
        {
            DataSet DS = new DataSet();
            DataTable dt = new DataTable();
            DataTable dtclone = new DataTable();
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                string CommandText = "select * from [" + TabName + "]";
                DB = new SQLiteDataAdapter(CommandText, sql_con);
                DS.Reset();
                DB.Fill(DS);
                dt = DS.Tables[0];

                dtclone = dt.Clone();

                dtclone.Columns["isChanget"].DataType = System.Type.GetType("System.Boolean");
                dtclone.Columns["isBad"].DataType = System.Type.GetType("System.Boolean");
                dtclone.Columns["isCheck"].DataType = System.Type.GetType("System.Boolean");
                dtclone.Columns["ID_IQ"].DataType = System.Type.GetType("System.Int32");


                foreach (DataRow dr in dt.Rows)
                {
                    dtclone.ImportRow(dr);
                }

                //dtclone = DS.Tables[0];

                sql_con.Close();
            }
            catch (Exception ex) { sql_con.Close(); }
            return dtclone;

        }
        public static DataTable GetSetData(string TabName)
        {
            DataSet DS = new DataSet();
            DataTable dt = new DataTable();
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                string CommandText = "select * from t_TableInfo where [Полный путь] = '" + TabName + "'";
                DB = new SQLiteDataAdapter(CommandText, sql_con);
                DS.Reset();
                DB.Fill(DS);
                dt = DS.Tables[0];

                sql_con.Close();
            }
            catch (Exception ex) { sql_con.Close(); }
            return dt;

        }
        public static bool CheckFile(string TabName)
        {
            int res = 0;
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "select 1 from t_TableInfo where [Полный путь] = '" + TabName + "'";
                res = Convert.ToInt32(sql_cmd.ExecuteScalar());

                sql_con.Close();
            }
            catch (Exception ex) { sql_con.Close(); }

            if (res == 1)
                return false;
            else
                return true;
        }
        public static string GetFileName(string TabName)
        {
            try
            {
                string res = "";
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "select [Имя файла] from t_TableInfo where [Полный путь] = '" + TabName + "'";
                res = Convert.ToString(sql_cmd.ExecuteScalar());

                sql_con.Close();
                return res;
            }
            catch (Exception ex) 
            {
                sql_con.Close(); 
                return "";
            }
        }
        public static string GetFileAddressIndex(string TabName)
        {
            try
            {
                string res = "";
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "select [Имя файла] from t_TableInfo where [Полный путь] = '" + TabName + "'";
                res = Convert.ToString(sql_cmd.ExecuteScalar());

                sql_con.Close();
                return res;
            }
            catch (Exception ex)
            {
                sql_con.Close();
                return "";
            }
        }
        public static void SetFileName(string TabName,string FileName)
        {
            try
            {
                SetConnection();
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "UPDATE t_TableInfo SET [Имя файла] = '" + FileName + "'  where [Полный путь] = '" + TabName + "'";
                sql_cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { sql_con.Close(); }
        }


        public static string GetAddrByID(string TabName, int ID)
        {
            string res = "0";
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "select * from [" + TabName + "] where [ID_IQ] = " + ID + " AND [textStatus] = '' ";

                using (SQLiteDataReader rdr = sql_cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        res = rdr[frmClean.cCfg.StartInd].ToString();
                    }
                }
                    
                    //res = Convert.ToString(sql_cmd.ExecuteScalar());
                    sql_cmd.Dispose();
                    sql_con.Close();
                  //  tr.Commit();
               // }
            }
            catch (Exception ex) 
            {
                sql_con.Close(); 
            }

            return res;

        }

        public static void UpdateRowByJSON(string TabName, string AddressOtvet, string Key)
        {
            templateResponse Address;
            try { Address = new JavaScriptSerializer().Deserialize<templateResponse>(AddressOtvet);}
            catch (Exception ex) { return; }

            if (Address == null) return;

            var itemAddr = Address.Address[0];
            string val = "";
            string text_status;
            string AddressString = itemAddr.c_address_full;

            //if (Address.Address.Count() == 1 && itemAddr.c_status_error == "0000")  { text_status = "Хороший";}
            //else if (itemAddr.msg == "Поиск не дал результатов!") { text_status = "Плохой"; }
            //else { text_status = "Сомнительный"; }

            try
            {
                if (!String.IsNullOrEmpty(itemAddr.c_zipcode)) val += "[iqPostCode] = '" + itemAddr.c_zipcode.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_address_full)) val += "[iqFullAddress] = '" + itemAddr.c_address_full.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_region_name)) val += "[iqRegion] = '" + itemAddr.c_region_name.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_region_abbr)) val += "[iqRegionType] = '" + itemAddr.c_region_abbr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_city_name)) val += "[iqCity] = '" + itemAddr.c_city_name.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_city_abbr)) val += "[iqCityType] = '" + itemAddr.c_city_abbr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_community_name)) val += "[iqNp] = '" + itemAddr.c_community_name.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_community_abbr)) val += "[iqNpType] = '" + itemAddr.c_community_abbr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_street_name)) val += "[iqStreet] = '" + itemAddr.c_street_name.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_street_abbr)) val += "[iqStreetType] = '" + itemAddr.c_street_abbr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_district_name)) val += "[iqSubRegion] = '" + itemAddr.c_district_name.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_district_abbr)) val += "[iqSubRegionType] = '" + itemAddr.c_district_abbr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_index_out)) val += "[iqPostCode] = '" + itemAddr.c_zipcode.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_kladr)) val += "[iqKLADR] = '" + itemAddr.c_kladr.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_status_error)) val += "[iqError] = '" + itemAddr.c_status_error.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_house_error_desc)) val += "[iqStatus] = '" + itemAddr.c_house_error_desc.ToString().Replace(";", ",").Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_addr_out)) val += "[iqFullAddress] = '" + itemAddr.c_addr_out.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.c_addr_lost)) val += "[iqLostAddr] = '" + itemAddr.c_addr_lost.ToString().Replace("'", "''") + "',";
                if (!String.IsNullOrEmpty(itemAddr.msg)) val += "[iqStatus] = '" + itemAddr.msg.ToString().Replace(";", ",").Replace("'", "''") + "',";

                if (itemAddr.c_json_kvant != null && !String.IsNullOrEmpty(itemAddr.c_json_kvant.ToString()))
                {
                    foreach (KeyValuePair<string, object> kvant in itemAddr.c_json_kvant as Dictionary<string, object>)
                    {
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "house")
                        {
                            val += "[iqHouse] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", дом " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "case")
                        {
                            val += "[iqCase] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", кор. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "build")
                        {
                            val += "[iqBuild] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", стр. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "liter")
                        {
                            val += "[iqLiter] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", лит. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "vlad")
                        {
                            val += "[iqVlad] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", влад. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "uch")
                        {
                            val += "[iqUch] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", уч. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "block")
                        {
                            val += "[iqBlock] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", блок " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "pav")
                        {
                            val += "[iqPav] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", пав. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "pom")
                        {
                            val += "[iqPom] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", пом. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "office")
                        {
                            val += "[iqOffice] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", офис " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "flat")
                        {
                            val += "[iqFlat] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", кв. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "kom")
                        {
                            val += "[iqKom] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", ком. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "vch")
                        {
                            val += "[iqVch] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", вч " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "abon")
                        {
                            val += "[iqAbon] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", а/я " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "kab")
                        {
                            val += "[iqKab] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", каб. " + kvant.Value.ToString().Replace("'", "''");
                        }
                        if (!String.IsNullOrEmpty(kvant.Key) && kvant.Key.ToLower() == "obsch")
                        {
                            val += "[iqObsch] = '" + kvant.Value.ToString().Replace("'", "''") + "',";
                            AddressString += ", общ. " + kvant.Value.ToString().Replace("'", "''");
                        }
                    }
                }


                //if ((isGood < 0) || (item.ErrorCode.ToString() == "") || (string.IsNullOrEmpty(item.post_code)) )
                if (/*Address.Address.Count() == 1 &&*/itemAddr.c_status_error == "0000")  
                {
                    val += "[iqWorkAddress] = '" + AddressString.Replace("'", "''") + "',";
                    val += "[isCheck] = 1,";
                    val += "[isBad] = 0,";
                    val += "[isChanget] = 0,";
                    val += "[textStatus] = 'Хороший',";
                }
                else
                {
                    val += "[iqWorkAddress] = '',";
                    val += "[isCheck] = 0,";
                    val += "[isBad] = 0,";
                    val += "[isChanget] = 0,";
                    val += "[textStatus] = 'Сомнительный',";
                }
            }
            catch(Exception ex)
            {
                val += "[iqWorkAddress] = '',";
                val += "[isCheck] = 0,";
                val += "[isBad] = 1,";
                val += "[isChanget] = 0,";
                val += "[iqStatus] = '" + itemAddr.msg.ToString().Replace(";", ",").Replace("'", "''") + "',";
                val += "[textStatus] = 'Плохой',";
            }     

            
            SetConnection();
            sql_con.Open();
            //using (SQLiteTransaction transSQLite = sql_con.BeginTransaction())
            //{
                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "UPDATE [" + TabName + "] SET " + val.Substring(0, val.Length - 1) + " WHERE [ID_IQ] = " + Key;
                sql_cmd.ExecuteNonQuery();

                sql_cmd.CommandText = "UPDATE [" + TabName + "_servresult] SET [service_result_json] = '" + AddressOtvet.Replace("'", "''") + "' WHERE [ID_IQ] = " + Key;
                sql_cmd.ExecuteNonQuery();
                sql_con.Close();

                //transSQLite.Commit();
            //}
        }

        public static int GetRecordCount(string TabName,int status = -1)
            //0-все прочищенные;1-хорошие;2-плохие;3-сомнительные;4-непрочищенные
        {
            int res;
            string cmd;
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            cmd = "select count(*) from [" + TabName + "] ";
            if (status == 0) cmd += " where [textStatus] != ''";
            if (status == 1) cmd += " where [textStatus] = 'Хороший'";
            if (status == 2) cmd += " where [textStatus] = 'Плохой'";
            if (status == 3) cmd += " where [textStatus] = 'Сомнительный'";
            if (status == 4) cmd += " where [textStatus] = ''";
            sql_cmd.CommandText = cmd;
            res = Convert.ToInt32(sql_cmd.ExecuteScalar());
            sql_con.Close();

            return res;
        }
        public static string GetJsonCleanResultByID(string TabName, int ID)
        {
            string res = "0";
            try
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "select [service_result_json] from [" + TabName + "_servresult] where [ID_IQ] = " + ID.ToString();

                res = Convert.ToString(sql_cmd.ExecuteScalar());
                sql_cmd.Dispose();
                sql_con.Close();
                //  tr.Commit();
                // }
            }
            catch (Exception ex)
            {
                sql_con.Close();
            }

            return res;
        }









    }
}
